﻿using MaynVideo;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Mayn.Video
{
    public static class StringExtensions
    {
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataTable FillDataTable(this string sql, object param = null)
        {
            DataTable dt = DbClient.Db.Ado.GetDataTable(sql, param);
            return dt;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataTable FillDataTableBySp(this string sql, object param = null)
        {
            var dt = DbClient.Db.Ado.UseStoredProcedure().GetDataTable(sql, param);
            return dt;
        }

        /// <summary>
        /// 查询分页
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="startIndex"></param>
        /// <param name="endIndex"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataTable FillDataTableByPage(this string sql, int startIndex, int endIndex, object param = null)
        {
            endIndex = endIndex - startIndex + 1;
            sql = "SELECT * FROM ("
                  + sql
                  + " ) A ";

            sql += $" LIMIT {startIndex - 1},{endIndex}";
            return sql.FillDataTable(param);
        }

        public static DataTable FillDataTableByPageNew(this string sql, int startIndex, int endIndex, object param = null)
        {
            sql = "SELECT * FROM ("
                  + sql
                  + " ) A ";

            sql += $" LIMIT {(startIndex - 1) * endIndex},{endIndex}";
            return sql.FillDataTable(param);
        }

        /// <summary>
        /// 执行sql返回受影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int Execute(this string sql, object param = null)
        {
            int affectedRows = DbClient.Db.Ado.ExecuteCommand(sql, param);
            return affectedRows;
        }

        /// <summary>
        /// 执行存储过程返回受影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int ExecuteSp(this string sql, object param = null)
        {
            int affectedRows = 0;
            try
            {
                affectedRows = DbClient.Db.Ado.UseStoredProcedure().ExecuteCommand(sql, param);
            }
            catch (Exception ex)
            {
                throw;
            }
            return affectedRows;
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="param">参数</param>
        /// <returns>bool</returns>
        public static bool ExecuteTrans(this string sql, object param = null)
        {
            var result = DbClient.Db.Ado.UseTran(() =>
            {
                DbClient.Db.Ado.ExecuteCommand(sql, param);
            });
            return result.IsSuccess;
        }

        /// <summary>
        /// 查询返回首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static object ExecuteScalar(this string sql, object param = null)
        {
            var result = DbClient.Db.Ado.GetScalar(sql, param);
            return result;
        }

        public static int ExecuteScalarInt(this string sql, object param = null)
        {
            var result = DbClient.Db.Ado.GetInt(sql, param);
            return result;
        }

        public static string ExecuteScalarString(this string sql, object param = null)
        {
            var result = DbClient.Db.Ado.GetString(sql, param);
            return result;
        }

        public static Double ExecuteScalarDouble(this string sql, object param = null)
        {
            var result = DbClient.Db.Ado.GetDouble(sql, param);
            return result;
        }

        public static Decimal ExecuteScalarDecimal(this string sql, object param = null)
        {
            var result = DbClient.Db.Ado.GetDecimal(sql, param);
            return result;
        }

        /// <summary>
        /// 查询返回首行
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QueryFirst<T>(this string sql, object param = null)
        {
            var result = DbClient.Db.Ado.SqlQuerySingle<T>(sql, param);
            return result;
        }

        /// <summary>
        /// 查询返回首行
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QueryFirstOrDefault<T>(this string sql, object param = null)
        {
            var result = DbClient.Db.Ado.SqlQuerySingle<T>(sql, param);
            return result;
        }

        /// <summary>
        /// 查询List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static List<T> QueryList<T>(this string sql, object param = null)
        {
            var result = DbClient.Db.Ado.SqlQuery<T>(sql, param);
            return result;
        }
    }
}
